/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.services.gl;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import src.HibernateUtil;
import src.domain.gl.Glbalance;
import src.domain.gl.Gledger;
import src.services.CRUEDService;
import src.util.Utils;

/**
 *
 * @author jasuke
 */
public class GledgerModel {

    public GledgerModel() {
    }
    
    public BigDecimal getSaldoAwal(Integer glaccntId, java.util.Date date, Integer branchId) {
        
        long ldate = date.getTime();
        java.sql.Date sqlStartDate = new java.sql.Date(ldate);
        String sql = 
                "select jv_actions.get_glbalance_awal("+Utils.fmtSQLDate(sqlStartDate)+","+glaccntId+","+branchId+") from dual";
        BigDecimal obj = (BigDecimal) new CRUEDService().getObjectSQLdirect(sql);
        
        if (obj==null) obj = new BigDecimal(0);
        return obj; 
    }
    
    public List<Gledger> loadData(Integer glaccntId, Integer branchId, 
            java.util.Date startDate, java.util.Date endDate) {
        
        BigDecimal saldo = getSaldoAwal(glaccntId, startDate, branchId);
        List<Gledger> hasil = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        ResultSet result;
        
        CallableStatement callableStatement;
        try {
            
            long sDate = startDate.getTime();
            long eDate = endDate.getTime();
            java.sql.Date sqlStartDate = new java.sql.Date(sDate);
            java.sql.Date sqlEndDate = new java.sql.Date(eDate);

            callableStatement = con.prepareCall("{call JV_REPORT.load_general_ledger(?,?,?,?,?)}");
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, glaccntId);
            callableStatement.setInt(3, branchId);
            callableStatement.setDate(4, sqlStartDate);
            callableStatement.setDate(5, sqlEndDate);

            callableStatement.executeQuery();
     
            result = (ResultSet) callableStatement.getObject(1);
            
            hasil.add(new Gledger(
                        null,  
                        null, 
                        null, 
                        "SALDO AWAL",
                        null,
                        null,
                        null, saldo));
            
            while (result.next()) {
            // if(result.next()) {
                //g.account_no, g.account_name, j.jv_date, j.jv_no, d.debit, d.credit
                saldo = saldo.add(result.getBigDecimal(6)).subtract(result.getBigDecimal(7));
                hasil.add(new Gledger(
                        result.getString(1),
                        result.getString(2), 
                        result.getString(3), 
                        result.getString(4), 
                        result.getDate(5),
                        result.getBigDecimal(6),
                        result.getBigDecimal(7),
                        saldo));
               
            }

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(GledgerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hasil;
    }
    
    public List<Glbalance> loadDataGLBalancePeriod(Integer branchId, 
            java.util.Date startDate, java.util.Date endDate) {
                
        List<Glbalance> hasil = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        ResultSet result;
        
        CallableStatement callableStatement;
        try {
            
            long sDate = startDate.getTime();
            long eDate = endDate.getTime();
            java.sql.Date sqlStartDate = new java.sql.Date(sDate);
            java.sql.Date sqlEndDate = new java.sql.Date(eDate);

            callableStatement = con.prepareCall("{call JV_REPORT.load_general_ledger_period(?,?,?,?)}");
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, branchId);
            callableStatement.setDate(3, sqlStartDate);
            callableStatement.setDate(4, sqlEndDate);

            callableStatement.executeQuery();
     
            result = (ResultSet) callableStatement.getObject(1);
                                    
            while (result.next()) {
            
                //v_account_no, v_account_name, v_account_type, v_awal, v_total, v_akhir
                hasil.add(new Glbalance(result.getString(1), result.getString(2), result.getString(3),
                        result.getBigDecimal(4), result.getBigDecimal(5), result.getBigDecimal(6)));
            }

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(GledgerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hasil;
    }
    
    public List<Glbalance> loadDataGLBalanceMonth(Integer branchId, Integer year) {
                
        List<Glbalance> hasil = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        ResultSet result;
                    
        CallableStatement callableStatement;
        try {

            callableStatement = con.prepareCall("{call JV_REPORT.load_general_ledger_month(?,?,?)}");
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, branchId);
            callableStatement.setInt(3, year);
            
            callableStatement.executeQuery();
     
            result = (ResultSet) callableStatement.getObject(1);
                                    
            while (result.next()) {
                hasil.add(new Glbalance(result.getString(1), result.getString(2), result.getString(3),
                        result.getBigDecimal(4), result.getBigDecimal(6), 
                        result.getBigDecimal(7), result.getBigDecimal(8), result.getBigDecimal(9),
                result.getBigDecimal(10), result.getBigDecimal(11), result.getBigDecimal(12),
                result.getBigDecimal(13), result.getBigDecimal(14), result.getBigDecimal(15), 
                result.getBigDecimal(16), result.getBigDecimal(17), result.getBigDecimal(18)));
            }

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(GledgerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hasil;
    }
    
    public List<Glbalance> loadDataGLBalanceTrial(Integer branchId, 
            java.util.Date startDate, java.util.Date endDate) {
            
        List<Glbalance> hasil = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        ResultSet result;
        
        CallableStatement callableStatement;
        try {
            
            long sDate = startDate.getTime();
            long eDate = endDate.getTime();
            java.sql.Date sqlStartDate = new java.sql.Date(sDate);
            java.sql.Date sqlEndDate = new java.sql.Date(eDate);

            callableStatement = con.prepareCall("{call JV_REPORT.load_trial_balance(?,?,?,?)}");
            callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callableStatement.setInt(2, branchId);
            callableStatement.setDate(3, sqlStartDate);
            callableStatement.setDate(4, sqlEndDate);

            callableStatement.executeQuery();
     
            result = (ResultSet) callableStatement.getObject(1);
                                    
            while (result.next()) {
            
                
                Glbalance trial = new Glbalance();
                trial.setAccountName(null);
                
                //accountNo, accountName, awal, saldo, total, total01, total02
                //v_account_no, v_account_name, null, v_awal*-1, v_change*-1, v_akhir*-1, v_debet, v_credit
                hasil.add(new Glbalance(result.getString(1), result.getString(2), 
                        result.getBigDecimal(4), result.getBigDecimal(5), 
                        result.getBigDecimal(6), result.getBigDecimal(7), result.getBigDecimal(8)));
                
            }

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(GledgerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return hasil;
    }
}
